<html>
  <head><title>SP Objects</title>
    <script src="codepress/codepress.js" type="text/javascript"></script> 
    <link rel="stylesheet" href="style.css" type="text/css" media="all" />
  </head>
  <body>

    <h1>Procedure Object: D_QT_SAP_LI.SQL</h1>
    <p><b>Author:</b> Bill Wang</p>
    <p><b>Date:</b> Mar. 28, 2007</p>
    <p><b>Abstract:</b></p>
    <p>  Delete all rows in the new web_quote_sap_line_item table for given key
  </b></p>
    <p><b>Sample Calls:</b></p>
    <p>    call EBIZ1.D_QT_SAP_LI (?, '0000047450')<br>  </b></p>
    <p></p>
    
    
    <h3>Tables:</h3>
    <ul>
      
        <li><b><a href="WEB_QUOTE_SAP_LINE_ITEM.html">EBIZ1.WEB_QUOTE_SAP_LINE_ITEM</a></b></li>
      
    </ul>
    
    
    
    
    
    
    
    
    <h3>Source code:</h3>
    <div id="SQLsrc" >
<em>DROP</em>&nbsp;SPECIFIC&nbsp;PROCEDURE&nbsp;EBIZ1.D_QT_SAP_LI<br />@<br /><em>CREATE</em>&nbsp;PROCEDURE&nbsp;EBIZ1.D_QT_SAP_LI&nbsp;(<br />&nbsp;&nbsp;OUT&nbsp;&nbsp;poGenStatus&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>INTEGER</u><br />&nbsp;&nbsp;,&nbsp;<b>IN</b>&nbsp;piWebQuoteNum&nbsp;&nbsp;&nbsp;&nbsp;<u>CHARACTER</u>(10)<br />)<br />&nbsp;&nbsp;<b>LANGUAGE</b>&nbsp;<b>SQL</b><br />&nbsp;&nbsp;MODIFIES&nbsp;<b>SQL</b>&nbsp;DATA<br />&nbsp;&nbsp;SPECIFIC&nbsp;EBIZ1.D_QT_SAP_LI<br /><i>-----------------------------------------------------------------------------------</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;*****&nbsp;IBM&nbsp;Confidential&nbsp;*****</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Procedure&nbsp;Name&nbsp;:&nbsp;D_QT_SAP_LI</i><br /><i>--&nbsp;&nbsp;Specific&nbsp;Name:&nbsp;&nbsp;&nbsp;D_QT_SAP_LI</i><br /><i>--&nbsp;&nbsp;File&nbsp;Name:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D_QT_SAP_LI.<b>SQL</b></i><br /><i>--&nbsp;&nbsp;Author:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Bill&nbsp;Wang</i><br /><i>--&nbsp;&nbsp;<u>Date</u>:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mar.&nbsp;28,&nbsp;2007</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Abstract:&nbsp;&nbsp;<em>Delete</em>&nbsp;<b>all</b>&nbsp;rows&nbsp;<b>in</b>&nbsp;the&nbsp;new&nbsp;web_quote_sap_line_item&nbsp;<b>table</b>&nbsp;<b>for</b>&nbsp;given&nbsp;<b>key</b></i><br /><i>--</i><br /><i>--&nbsp;&nbsp;Sample&nbsp;Calls:</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;call&nbsp;EBIZ1.D_QT_SAP_LI&nbsp;(?,&nbsp;<s>'0000047450'</s>)</i><br /><i>--</i><br /><i>--&nbsp;&nbsp;MAINTENANCE&nbsp;LOG</i><br /><i>--&nbsp;&nbsp;who&nbsp;&nbsp;&nbsp;<u>date</u>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b><em>comment</em></b></i><br /><i>--&nbsp;&nbsp;---&nbsp;&nbsp;&nbsp;--------&nbsp;&nbsp;---------------------------------------------------------------</i><br /><i>--&nbsp;&nbsp;Bill&nbsp;&nbsp;03/28/07&nbsp;&nbsp;Initial&nbsp;Version</i><br /><i>--</i><br /><i>-----------------------------------------------------------------------------------</i><br />P1:&nbsp;<em>BEGIN</em>&nbsp;<b>NOT</b>&nbsp;ATOMIC<br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Variables&nbsp;declarations</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Generic&nbsp;Variables</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;SQLCODE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>INTEGER</u>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>DEFAULT</b>&nbsp;0;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;SQLSTATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHAR</u>(5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>DEFAULT</b>&nbsp;<s>'00000'</s>;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Generic&nbsp;handler&nbsp;variables</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;hSqlcode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>INTEGER</u>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>DEFAULT</b>&nbsp;0;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;hSqlstate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHAR</u>(5)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>DEFAULT</b>&nbsp;<s>'00000'</s>;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;CONDITION&nbsp;declaration</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;(80100~80199)&nbsp;SQLCODE&nbsp;&&nbsp;SQLSTATE</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;sqlReset&nbsp;CONDITION&nbsp;<b>for</b>&nbsp;sqlstate&nbsp;<s>'80100'</s>;<br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;<b>EXCEPTION</b>&nbsp;HANDLER&nbsp;declaration</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Handy&nbsp;Handler</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;CONTINUE&nbsp;HANDLER&nbsp;<b>FOR</b>&nbsp;sqlReset<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>BEGIN</em>&nbsp;<b>NOT</b>&nbsp;ATOMIC<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;hSqlcode&nbsp;&nbsp;&nbsp;=&nbsp;0;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;hSqlstate&nbsp;&nbsp;=&nbsp;<s>'00000'</s>;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;0;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Generic&nbsp;Handler</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;CONTINUE&nbsp;HANDLER&nbsp;<b>FOR</b>&nbsp;SQLEXCEPTION,&nbsp;SQLWARNING,&nbsp;<b>NOT</b>&nbsp;FOUND<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>BEGIN</em>&nbsp;<b>NOT</b>&nbsp;ATOMIC<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Capture&nbsp;SQLCODE&nbsp;&&nbsp;SQLSTATE</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SELECT</em>&nbsp;&nbsp;SQLCODE,&nbsp;SQLSTATE<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>INTO</b>&nbsp;&nbsp;&nbsp;&nbsp;hSqlcode,&nbsp;hSqlstate<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>FROM</b>&nbsp;&nbsp;&nbsp;&nbsp;SYSIBM.SYSDUMMY1;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Use&nbsp;the&nbsp;hGenStatus&nbsp;variable&nbsp;<b>to</b>&nbsp;tell&nbsp;the&nbsp;procedure&nbsp;what&nbsp;<b>type</b></i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;<b>of</b>&nbsp;error&nbsp;occurred.&nbsp;&nbsp;<b>In</b>&nbsp;some&nbsp;cases,&nbsp;it&nbsp;can&nbsp;be&nbsp;assigned&nbsp;<b>to</b>&nbsp;the</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;poGenStatus&nbsp;variable&nbsp;<b>to</b>&nbsp;be&nbsp;returned&nbsp;<b>to</b>&nbsp;the&nbsp;client.</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASE</b>&nbsp;hSqlstate<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;<s>'02000'</s>&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;5000;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;<s>'42724'</s>&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;3;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>ELSE</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;2;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>CASE</b>;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Initialization</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;<em>reset</em>&nbsp;<b>all</b>&nbsp;output&nbsp;parameters&nbsp;<b>to</b>&nbsp;<b>NULL</b></i><br />&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;0;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;make&nbsp;sure&nbsp;<b>all</b>&nbsp;required&nbsp;<b>input</b>&nbsp;parameters&nbsp;are&nbsp;<b>not</b>&nbsp;<b>null</b></i><br />&nbsp;&nbsp;&nbsp;&nbsp;<b>IF</b>&nbsp;((COALESCE(RTRIM(piWebQuoteNum),&nbsp;<s>''</s>)&nbsp;=&nbsp;<s>''</s>))&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;34100;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>RETURN</b>&nbsp;poGenStatus;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>IF</b>;<br /><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Procedure&nbsp;Body</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;<em>Reset</em>&nbsp;the&nbsp;<b>SQL</b>&nbsp;<b>values</b>&nbsp;<b>after</b>&nbsp;every&nbsp;<em>select</em>,&nbsp;<em>update</em>,&nbsp;<em>insert</em>&nbsp;&&nbsp;<em>delete</em></i><br />&nbsp;&nbsp;&nbsp;&nbsp;SIGNAL&nbsp;sqlReset;<br />&nbsp;&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;<em>DELETE</em>&nbsp;<b>FROM</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;EBIZ1.WEB_QUOTE_SAP_LINE_ITEM<br />&nbsp;&nbsp;&nbsp;&nbsp;<b>WHERE</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WEB_QUOTE_NUM&nbsp;=&nbsp;piWebQuoteNum;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;<b>Return</b>&nbsp;the&nbsp;<b>return</b>&nbsp;status</i><br />&nbsp;&nbsp;&nbsp;&nbsp;<b>RETURN</b>&nbsp;poGenStatus;<br /><br /><em>END</em>&nbsp;P1<br /><br />@<br /><br />CALL&nbsp;DTOL.g_sp_exec_grp&nbsp;(<s>'EBIZ1'</s>,&nbsp;<s>'D_QT_SAP_LI'</s>,&nbsp;<s>'prcbkweb'</s>)<br />@<br />
    </div> 
  </body>
</html>
